drop table incarico;
drop table legislatura;
drop table partito;
drop table parlamentare;
drop table pagamenti_correnti;
drop type incaricoty;
drop type parlamentarety;
drop type legislaturaty;
drop type REF_PartitoNT ;
drop type ref_partitoty;
drop type partitoty;


create or replace type partitoty as object (nome varchar2(30))
/

create or replace type ref_partitoty as object (partito ref partitoty)
/

create or replace type REF_PartitoNT as table of ref_partitoty
/
create or replace type legislaturaty as object (ID int, data_inizio DATE, DATA_FINE date)
/

create or replace type parlamentarety as object (CF varchar2(16), nome varchar2(30), cognome varchar2(30), data_nascita date, salary NUMERIC(20,2), partito ref partitoty, partitopassato REF_PartitoNT)
/

create or replace type incaricoty as object (ID integer, data_inizio date, data_fine date, num_presenze number, circoscrizione varchar(30), legislatura ref legislaturaty, parlamentare ref parlamentarety)
/

create table incarico of incaricoty (ID primary key);
/

create table legislatura of legislaturaty (ID primary key);
/



create table partito of partitoty (nome primary key);
/



create table parlamentare of parlamentarety (CF primary key) nested table partitopassato store as partitopassato_NT
/


insert into legislatura values (legislaturaty(1, '13-nov-2001', '13-nov-2004'));

select * from legislatura;

insert into partito values (partitoty('ds'));
insert into partito values (partitoty('FI'));

insert into parlamentare select parlamentarety('CCEMHL', 'michelangelo','ceci', '13-nov-1976',200000, ref(p), null ) from partito p where p.nome='ds';

select * from parlamentare;

select deref(partito).nome from parlamentare;


update parlamentare set partitopassato = cast(multiset(select ref(p) from partito p where p.nome='FI')as REF_PartitoNT) where CF='CCEMHL';

select deref(partito).nome from parlamentare;

select deref(ParNT.partito).nome  from TABLE(select partitopassato from parlamentare where CF='CCEMHL') ParNT;


insert into parlamentare select parlamentarety(DBMS_RANDOM.STRING('U',16),DBMS_RANDOM.STRING('U',10),DBMS_RANDOM.STRING('U',10), TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J')
                                    ,TO_CHAR(DATE '9999-12-31','J')
                                    )
                    ),'J'
               ) ,mod(DBMS_RANDOM.random,200000), ref(p), null ) from partito p where p.nome='ds';


drop procedure populate_parlamentari;
create procedure populate_parlamentari as
iterations number;
begin
iterations:=0;
loop
  insert into parlamentare select parlamentarety(DBMS_RANDOM.STRING('U',16),DBMS_RANDOM.STRING('U',10),DBMS_RANDOM.STRING('U',10), TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J')
                                    ,TO_CHAR(DATE '9999-12-31','J')
                                    )
                    ),'J'
               ) , mod(DBMS_RANDOM.random,200000), ref(p), null ) from partito p where p.nome='ds';
 iterations := iterations +1;
  exit when iterations=20000;
end loop;
end;
/

exec populate_parlamentari;


Create table pagamenti_correnti (
parlamentare ref parlamentarety, pagamento NUMERIC(8,2));

create or replace trigger valida_gettone 
after update of num_presenze on incarico
for each row
DECLARE
gettone NUMERIC(8,2):= 200;
num_pagamenti_correnti NUMBER;
salario NUMERIC(8,2);
begin
select count(*) into num_pagamenti_correnti from pagamenti_correnti where parlamentare = :new.parlamentare;
select salary into salario from parlamentare p where ref(p)=:new.parlamentare;
   if (num_pagamenti_correnti=0) then
           begin
          insert into pagamenti_correnti values (:new.parlamentare, salario+gettone*(:new.num_presenze-:old.num_presenze) );
          end;
       ELSE
           update pagamenti_correnti Set pagamento= pagamento + gettone*(:new.num_presenze-:old.num_presenze) where parlamentare= :new.parlamentare;
       END IF;
end;
/

insert into incarico select 1,'13-nov-2001', '13-nov-2004',0,0,(select ref(l) from legislatura L where id=1),(select ref(p) from parlamentare P where CF='CCEMHL') from dual;

select * from incarico;

select * from pagamenti_correnti;


update incarico set num_presenze=num_presenze+1 where PARLAMENTARE= (select ref(p) from parlamentare p where CF='CCEMHL');

select * from incarico;

select * from pagamenti_correnti;

update incarico set num_presenze=num_presenze+1 where PARLAMENTARE= (select ref(p) from parlamentare p where CF='CCEMHL');

select * from incarico;

select * from pagamenti_correnti;

select add_months(last_day(sysdate)+1,-1) from dual;

--connect as system
--grant select on PARLAMENTO.incarico to parlamento_dw;
--grant select on PARLAMENTO.legislatura to parlamento_dw;
--grant select on PARLAMENTO.partito to parlamento_dw;
--grant select on PARLAMENTO.parlamentare to parlamento_dw;

commit;